﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;

namespace SugarSpeed
{
    class Program 
    {
        static void Main(string[] args)
        {
            int count = 10;
            string msg = "SqlSugar列表查询";
            //string msg = "Sql语句列表查询";
            Stopwatch sw = new Stopwatch();
            sw.Start();
            
            for (int i = 0; i < count; i++)
            {
                SugarTest();
                //SqlTest();
                Console.Write(".");
            }
            sw.Stop();
            Console.WriteLine($"{msg},次数：{count}，总耗时：{sw.ElapsedMilliseconds}毫秒!");

            Console.ReadKey();
        }

        static void SugarTest()
        {
            int pageindex = 100;
            int pagesize = 15; 
            var db = GetSuarClient();
            SearchC sInfo = new SearchC() 
            {
                sOrgId = "41166b0a-7d9d-4e22-9aa8-92bc624d9be1",
                sOrgGroupId = "88bde69f-5ce4-43fd-ad84-5eab202ffec6",
                sOrgCode = "YYS122",
                sAreaCode = "350212",
                iLevel = 3
            };
            int total = 0;
            var list = db.Queryable<VisitingRecord>().Where(p => p.iDelete == 0)
                        //.WhereIF(!string.IsNullOrEmpty(sInfo.sOrgId), p => p.sOrgId == sInfo.sOrgId)
                        //.WhereIF(!string.IsNullOrEmpty(sInfo.sOrgGroupId), p => p.sOrgGroupId == sInfo.sOrgGroupId)
                        .WhereIF(!string.IsNullOrEmpty(sInfo.sJGOrgCode), p => p.sOrgCode == sInfo.sJGOrgCode)
                        .WhereIF(sInfo.DateFrom.HasValue, p => p.dVisitTime >= sInfo.DateFrom)
                        .WhereIF(sInfo.DateTo.HasValue, p => p.dVisitTime < sInfo.DateTo.Value.AddDays(1))
                        .WhereIF(sInfo.iFull.HasValue, p => p.iFull == sInfo.iFull.Value)
                        .WhereIF(sInfo.iLevel == 1, p => p.Province == sInfo.sAreaCode)
                        .WhereIF(sInfo.iLevel == 2, p => p.City == sInfo.sAreaCode)
                        .WhereIF(sInfo.iLevel == 3, p => p.County == sInfo.sAreaCode)
                        .WhereIF(sInfo.iLevel == 4, p => p.Street == sInfo.sAreaCode)
                        .WhereIF(sInfo.iLevel == 5, p => p.Community == sInfo.sAreaCode)
                        .OrderBy(p => p.dAddTime, OrderByType.Desc)
                        .ToPageList(pageindex, pagesize);
        }

        static void SqlTest()
        {
            var sql = @" with data as(
                      SELECT  ROW_NUMBER() OVER ( ORDER BY a.dAddTime DESC ) rownumber,a.*
                       FROM VisitingRecord a where  a.idelete=0 and iFull=1 

                       )  select (select COUNT(1) from data) as Total,* 
                            from data where  rownumber > @startRow
                            AND rownumber <= @endRow";

            List<SqlSugar.SugarParameter> plist = new List<SugarParameter>();
            plist.Add(new SugarParameter("startRow", 0));
            plist.Add(new SugarParameter("endRow", 15));
            //plist.Add(new SugarParameter("sOrgCode", "YYS122"));

            var db = GetSuarClient();
            var data = db.Ado.SqlQuery<dynamic>(sql,plist);
        }

        static SqlSugarClient GetSuarClient()
        {
            return new SqlSugarClient(new ConnectionConfig()
            {
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.SystemTable,
                DbType = DbType.SqlServer,
                ConnectionString = "Data Source=192.168.44.170,53580;Initial Catalog=YLJGDB_Basic20211125;User ID=jqkj;Password=jqkj123$%^;"
            });
        }
    }
}
